# -*- coding: utf-8 -*-
# author: 华测-长风老师
import pymysql


class Session(object):
    # 初始化对象，产生一个mysql连接
    def __init__(self, host, user, password, database, port=3306, charset='utf8'):
        self._host = host
        self._user = user
        self._password = password
        self._database = database
        self._port = port
        self._charset = charset
        self._conn = None
        self._cursor = None
        try:
            self._conn = pymysql.connect(
                host=self._host,
                user=self._user,
                password=self._password,
                database=self._database,
                charset=self._charset,
                port=self._port,
                autocommit=True
            )
        except Exception as err:
            format_err = f"ERROR - {self._host} session init failed: {err}" + "\n"
            raise Exception(format_err)

    # 调用with方法的入口
    def __enter__(self):
        return self

    # # 调用with方法结束时启动
    def __exit__(self, exc_type, exc_val, exc_tb):
        self._conn.close()

    # 查询函数
    def query(self, sql):
        """
        :param sql: 查询语句
        :return: result-查询结果;
        """
        cursor = self._conn.cursor(pymysql.cursors.DictCursor)  # 设置返回数据为字典返回
        try:
            result = []
            rows = cursor.execute(sql)
            if rows > 0:
                sql_result = cursor.fetchall()
                # result = [list(i) for i in sql_result]
                result = sql_result
            cursor.close()
            return result
        except Exception as err:
            format_err = f"ERROR - {self._host} query failed: {err} - {sql}" + "\n"
            raise Exception(format_err)
        finally:
            cursor.close()

    # 单条dml语句
    def change(self, sql):
        """
        :param sql: 操作语句
        :return : rows-改动的记录条数
        """
        cursor = self._conn.cursor()
        try:
            rows = cursor.execute(sql)
            return rows
        except Exception as err:
            format_err = f"ERROR - {self._host} change failed: {err} - {sql}" + "\n"
            raise Exception(format_err)
        finally:
            cursor.close()

    # 多条dml语句
    def change_many(self, sql, value_list):
        """
        :param sql: 修改语句
        :param value_list: 参数值列表
        :return:rows-改动的记录条数
        """
        cursor = self._conn.cursor()
        try:
            rows = cursor.executemany(sql, value_list)
            return rows
        except Exception as err:
            format_err = f"ERROR - {self._host} change_many failed: {err} - {sql}" + "\n"
            raise Exception(format_err)
        finally:
            cursor.close()


# if __name__ == "__main__":
#     with Session(host="shop-xo.hctestedu.com", user="api_test", password="Aa9999!", database="shopxo_hctested") as session:
#         # sql_1 = "select username from sxo_user where id='983';"
#         sql_ = "select username from sxo_user where id='123';"
#
#         # row1 = session.query(sql_1)
#         row = session.query(sql_)
#         # print(row1)
#         print(row)

    # session = Session(ip="shop-xo.hctestedu.com", user="api_test", passwd="Aa9999!", db="shopxo_hctested")
    # sql_ = "select username from sxo_user where id='983';"
    #
    # row = session.query(sql_)
    # print(row)
